/* QAD_Item-Site_Formula_Report.p - ITEM-SITE FORMULA STRUCTURE REPORT        */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy fmpsrp10.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}
{{&US_PX}pxmaint.i}
{{&US_PX}pxphdef.i pprevxr}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}
   
/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table ttParent no-undo
   field ps_comp    like ps_mstr.ps_comp
   field batch_size like bom_mstr.bom_batch
   field um         like pt_mstr.pt_um
   field phantom    as   logical
   field iss_pol    as   logical
   field alt_part   like ptal_mstr.ptal_part
   field pt_desc    like pt_mstr.pt_desc1
   field ptp_rev    like ptrv_det.ptrv_rev
   field bom_code   like bom_mstr.bom_code
   field unique_id  as   integer
.

define temp-table ttComponent no-undo like ps_mstr
   field lvl        as   character
   field batch_size like bom_mstr.bom_batch
   field um         like pt_mstr.pt_um
   field phantom    as   logical
   field iss_pol    as   logical
   field alt_part   like ptal_mstr.ptal_part
   field pt_desc    like pt_mstr.pt_desc1
   field ptp_rev    like ptrv_det.ptrv_rev
   field bom_code   like bom_mstr.bom_code
   field unique_id  as   integer
   field ref_id     like ttparent.unique_id
   index idx unique_id
.

define temp-table ttReportOptions no-undo
   field op           like ro_det.ro_op
   field site         like ptp_det.ptp_site
   field eff_date     as   date
   field maxlevel     as   integer
   field skpge        like mfc_ctrl.mfc_logical
   field sort_ref     like mfc_ctrl.mfc_logical
   field formula_only like mfc_ctrl.mfc_logical
.

define dataset dsReportResults for ttParent, ttComponent, ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}

define variable vhDS as handle    no-undo.

empty temp-table ttParent        no-error.
empty temp-table ttComponent     no-error.
empty temp-table ttReportOptions no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_Item-Site_Formula_Report.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle("ttReportOptions",
         "site", "DefaultValue", global_site).

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
      "skpge", "DataFormat", getLabel("YES/NO")).

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
      "sort_ref", "DataFormat", getLabel("YES/NO")).

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
      "formula_only", "DataFormat", getLabel("YES/NO")).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString   as character no-undo.
   define variable hMainQuery    as handle    no-undo.
   define query    MainQuery     for pt_mstr, ptal_mstr.
define variable formula_only like mfc_logical no-undo.
define variable site       like ptp_site no-undo.
define variable comp       like ps_comp no-undo.
define variable level      as integer no-undo.
define variable maxlevel   as integer no-undo.
define variable itemIssuePolicy like pti_iss_pol no-undo.
define variable new_parent like mfc_logical no-undo.

define new shared variable parent     like ps_par no-undo.
define new shared variable phantom    like mfc_logical no-undo.
define new shared variable iss_pol    as logical no-undo.
define new shared variable bom_code   like pt_bom_code no-undo.





   assign
      hMainQuery     = query MainQuery:handle
      site           = GetFilterValue("ttReportOptions",
                          "site", global_site)
      eff_date       = GetDateFilterValue("ttReportOptions",
                          "eff_date", TODAY)
      maxlevel       = Integer(GetFilterValue("ttReportOptions",
                          "maxlevel",""))
      skpge          = IsTrueValue("ttReportOptions",
                          "skpge", false)
      sort_ref       = IsTrueValue("ttReportOptions",
                          "sort_ref", false)
      formula_only   = IsTrueValue("ttReportOptions",
                          "formula_only", false)
      .

   /* Add this do loop so the converter will not create 'on leave' */
   do:
      if not can-find(si_mstr where si_mstr.si_domain = global_domain
         and si_site = site)
      then do:
         {us/bbi/pxmsg.i &MSGNUM=708 &ERRORLEVEL=3} /* SITE DOES NOT EXIST. */
         retrun.
      end.
   end. /* end of do: */

   queryString = "for each pt_mstr no-lock where pt_domain = "
                  + quoter(global_domain).

   run AddSpecificConditions in reportHandle
      ("ps_mstr", "ps_par:pt_part", input-output queryString).

   queryString = queryString + " first ptal_mstr no-lock ".

   if not IsConditionEmpty("ttParent", "ptal_alt_part")
   then do:
      queryString = queryString + " where ptal_mstr.ptal_domain = "
                     + quoter(global_domain)
                     + " and ptal_mstr.ptal_part = pt_mstr.pt_part "
                     + " and ptal_mstr.ptal_active ".

      run AddAllConditions in reportHandle
         ( "ptal_mstr" , input-output queryString ).
   end. /* if not IsConditionEmpty(...) */

   queryString = queryString + ":".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:

      assign
         comp     = pt_part
         phantom  = pt_phantom
         bom_code = if pt_bom_code <> "" then pt_bom_code else pt_part
         maxlevel = min(maxlevel,99)
         iss_pol  = if pt_iss_pol = {&ISSUE-POLICY-NONE} then no else yes
         level    = 1.

      for first ptp_det no-lock where ptp_det.ptp_domain = global_domain 
         and ptp_part = pt_part
         and ptp_site = site no-lock:
      end. /* for first ptp_det */

      if available ptp_det
      then do:
         bom_code = if ptp_bom_code <> "" then ptp_bom_code else ptp_part.
      end /* if available ptp_det */

      {{&US_PX}pxrun.i &PROC  = 'getFieldDefault' &PROGRAM = 'ppitxr.p'
         &HANDLE = ph_ppitxr
         &PARAM = "(input pt_part,
                    input  site,
                    input ""pt_iss_pol"",
                    output itemIssuePolicy)"
         &NOAPPERROR = true
         &CATCHERROR = true}
      iss_pol = if itemIssuePolicy = {&ISSUE-POLICY-NONE} then no else yes.

      assign
         comp        = bom_code
         parent      = pt_part
         phantom     = if available ptp_det then ptp_phantom else phantom
         new_parent  = yes.

      for first bom_mstr
         where bom_mstr.bom_domain = global_domain
         and   bom_parent = bom_code
         and   (formula_only = no
                or bom_formula)
      no-lock:
      end. /* FOR FIRST bom_mstr */

      if not available bom_mstr
      then do:
         hMainQuery:get-next().
         next.
      end. /* if not available bom_mstr */
      else do:
         /* Changed entire logic to Recursive calling of Internal Procedure */
         run process_report (input comp,input level, input new_parent).

      end. /* IF AVAILABLE bom_mstr */

      hMainQuery:get-next().
   end. /* REPEAT QUERY */

   global_site = site.

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.

END PROCEDURE.

PROCEDURE process_report:
   define input parameter comp like ps_comp no-undo.
   define input parameter level as integer no-undo.
   define input parameter new_parent like mfc_logical.
   
   define variable subQueryString   as character no-undo.
   define variable hSubQuery    as handle    no-undo.
   define query    SubQuery     for ps_mstr.
   
   define query q_ps_mstr for ps_mstr.
   define buffer ptmstr for pt_mstr.

   define variable ptRev  like ptrv_rev no-undo.
   define variable ptpRev like ptrv_rev no-undo.


define variable skpge      like mfc_logical no-undo.
define variable sort_ref   like mfc_logical no-undo.
define variable eff_date   like exr_start_date no-undo.
define variable desc1      like pt_desc1 no-undo.
define variable um         like pt_um no-undo.
define variable lvl        as character no-undo.
define variable lines      as integer no-undo.
define variable batch_size like bom_batch no-undo.


define variable formula_only like mfc_logical no-undo.
define variable site       like ptp_site no-undo.
define variable maxlevel   as integer no-undo.
define variable itemIssuePolicy like pti_iss_pol no-undo.

define shared variable parent     like ps_par no-undo.
define shared variable phantom    like mfc_logical no-undo.
define shared variable iss_pol as logical no-undo.
define shared variable bom_code   like pt_bom_code no-undo.

   assign
      hSubQuery      = query SubQuery:handle
      site           = GetFilterValue("ttReportOptions",
                          "site", global_site)
      eff_date       = GetDateFilterValue("ttReportOptions",
                          "eff_date", TODAY)
      maxlevel       = Integer(GetFilterValue("ttReportOptions",
                          "maxlevel",""))
      skpge          = IsTrueValue("ttReportOptions",
                          "skpge", false)
      sort_ref       = IsTrueValue("ttReportOptions",
                          "sort_ref", false)
      formula_only   = IsTrueValue("ttReportOptions",
                          "formula_only", false)
      .

   for first ptp_det
      where ptp_det.ptp_domain = global_domain
      and   ptp_part           = comp
      and   ptp_site           = site
      no-lock:
   end. /* FOR FIRST ptp_det */
   
   if available ptp_det
   and ptp_bom_code <> ""
   then
      comp = ptp_bom_code.
   else
      for first pt_mstr
        where pt_mstr.pt_domain = global_domain
        and   pt_part           = comp
        no-lock:
      end. /* FOR FIRST pt_mstr */


   subQueryString = "for each ps_mstr no-lock where ps_mstr.ps_domain = "
                     + quoter(global_domain)
                     + " and ps_par = "
                     + quoter(comp)
                     + " and ps_ps_code <> 'J'".
   run AddSpecificConditions in reportHandle
      ("ro_det", "ro_op", input-output subQueryString).
   
   if sort_ref 
   then do:
      subQueryString = subQueryString + " use-index ps_parref:".
   end.
   else do:
      subQueryString = subQueryString + " use-index ps_parcomp:".
   end.

   hSubQuery:query-prepare(subQueryString).
   hSubQuery:query-open().
   hSubQuery:get-next().

   repeat while not hSubQuery:query-off-end:
   
   if new_parent = yes then do:

      for first ptp_det
         where ptp_det.ptp_domain = global_domain 
         and   ptp_part = comp
         and   ptp_site = site
         no-lock:
      end. /* FOR FIRST ptp_det */

      if available ptp_det
      then do:
         for first bom_mstr
             where bom_mstr.bom_domain = global_domain
             and   bom_parent = if ptp_bom_code <> ""
                                then ptp_bom_code
                                else comp
         no-lock:
         end. /* FOR FIRST bom_mstr */
      end. /* if available ptp_det */
      else do:
         for first bom_mstr
            where bom_mstr.bom_domain = global_domain
            and  bom_parent = if available pt_mstr and pt_bom_code <> ""
                              then pt_bom_code
                              else comp
         no-lock:
         end. /* FOR FIRST bom_mstr */
      end. /* else do */

      if available bom_mstr
      then do:
         assign
            batch_size  = bom_batch
            um          = bom_batch_um.
      end. 
      else do:
         um = if available pt_mstr
              then
                 pt_um
              else "".
      end.

      create ttParent.
      assign
         ttParent.ps_comp     = parent
         ttParent.batch_size  = batch_size
         ttParent.um          = um
         ttParent.phantom     = phantom
         ttParent.iss_pol     = iss_pol.

      if available pt_mstr and pt_desc1 <> ""
      then do:
         ttParent.pt_desc = pt_desc1 + "~n". 
      end.

      if available pt_mstr and pt_desc2 > ""
      then do:
         ttParent.pt_desc = ttParent.pt_desc + pt_desc2.
      end.

      {{&US_PX}pxgetph.i pprevxr}
      if available ptp_det then do:
         ptpRev = {{&US_PX}pxfunct.i 
                     &FUNCTION = 'getValidItemRevision'
                     &PROGRAM  = 'pprevxr.p'
                     &HANDLE   = ph_pprevxr
                     &PARAM    = "input ptp_part,
                                  input ptp_site,
                                  input eff_date"}.

         if ptpRev <> "" then do:
            ttParent.ptpRev = ptpRev.
         end.
      end. /* if available ptp_det */

      else if available pt_mstr 
      then do:
         ptRev = {{&US_PX}pxfunct.i 
                     &FUNCTION = 'getValidItemRevision'
                     &PROGRAM  = 'pprevxr.p'
                     &HANDLE   = ph_pprevxr
                     &PARAM    = "input pt_part,
                                  input '',
                                  input eff_date"}.
         if ptRev <> "" then do:
            ttParent.ptpRev = ptRev.
         end.
      end. /* else if available pt_mstr */
      if bom_code <> parent then do:
         ttParent.bom_code = bom_code.
      end.
      new_parent = no.
      end.  /* if new_parent = yes */


      if eff_date = ? or (eff_date <> ?
         and (ps_start = ? or ps_start <= eff_date) 
         and (ps_end = ? or eff_date <= ps_end))
      then do:

         assign
            um       = ""
            desc1    = getTermLabel("ITEM_NOT_IN_INVENTORY",24)
            iss_pol  = no
            phantom  = no
            bom_code = ps_comp.

         for first ptmstr  where ptmstr.pt_domain = global_domain
            and ptmstr.pt_part = ps_mstr.ps_comp
         no-lock:
         end.
         if available ptmstr 
         then do:
            assign
               um       = ptmstr.pt_um
               desc1    = ptmstr.pt_desc1
               iss_pol  = if ptmstr.pt_iss_pol = {&ISSUE-POLICY-NONE} 
                          then no
                          else yes
               phantom  = ptmstr.pt_phantom
               bom_code = if ptmstr.pt_bom_code <> ""
                          then ptmstr.pt_bom_code
                          else ptmstr.pt_part.
         end. /* if available ptmstr */
         else do:
            for first bom_mstr no-lock  where bom_mstr.bom_domain = global_domain
               and  bom_parent = ps_comp no-lock:
            end.
            if available bom_mstr then
               assign
                  um       = bom_batch_um
                  desc1    = bom_desc
                  bom_code = bom_parent.
         end.

         for first ptp_det no-lock  
            where ptp_det.ptp_domain = global_domain 
            and   ptp_part = ps_comp
            and   ptp_site = site:
         end.
         if available ptp_det
         then do:
            assign
               phantom  = ptp_phantom
               bom_code = if ptp_bom_code <> ""
                          then ptp_bom_code
                          else ptp_part.
         end.

         {{&US_PX}pxrun.i &PROC = 'getFieldDefault' 
            &PROGRAM = 'ppitxr.p'
            &HANDLE = ph_ppitxr
            &PARAM = "(input ps_comp,
                       input  site,
                       input ""pt_iss_pol"",
                       output itemIssuePolicy)"
            &NOAPPERROR = true
            &CATCHERROR = true}
         
         iss_pol = if itemIssuePolicy = {&ISSUE-POLICY-NONE} then no else yes.

         lvl = "........".
         lvl = substring(lvl,1,min (level - 1,9)) + string(level).
         if length(lvl) > 10 then
            lvl = substring(lvl,length (lvl) - 9,10).

         lines = 1.
         if ps_rmks > "" then lines = lines + 1.
         if available ptmstr and ptmstr.pt_desc1 > ""
         then
            lines = lines + 1.
         if available ptmstr and ptmstr.pt_desc2 > ""
         then
            lines = lines + 1.

         if available ptp_det 
         then do:
             {{&US_PX}pxgetph.i pprevxr}
              ptpRev = {{&US_PX}pxfunct.i 
                           &FUNCTION = 'getValidItemRevision'
                           &PROGRAM  = 'pprevxr.p'
                           &HANDLE   = ph_pprevxr
                           &PARAM    = "input ptp_part,
                                        input ptp_site,
                                        input eff_date"}.
         end.
         if ptpRev > ""
         then do:
            lines = lines + 1.
         end.
         else do:
            {{&US_PX}pxgetph.i pprevxr}
            if available ptmstr
            then do:
               ptRev = {{&US_PX}pxfunct.i 
                           &FUNCTION = 'getValidItemRevision'
                           &PROGRAM  = 'pprevxr.p'
                           &HANDLE   = ph_pprevxr
                           &PARAM    = "input ptmstr.pt_part,
                                        input '',
                                        input eff_date"}.
            end.
            if ptRev > ""
            then do:
               lines = lines + 1.
            end.
         end.

         for first bom_mstr no-lock 
            where bom_mstr.bom_domain = global_domain 
            and   bom_parent = ps_comp:
         end.

         if available bom_mstr
         then do:
            batch_size = bom_batch.
         end.

         create ttComponent.
         buffer-copy ps_mstr to ttComponent no-error.
         assign
            ttComponent.lvl         = lvl
            ttComponent.batch_size  = batch_size 
               when available bom_mstr and batch_size <> 1
            ttComponent.um          = um
            ttComponent.phantom     = phantom
            ttComponent.iss_pol     = iss_pol

         if desc1 > "" then do:
            ttComponent.pt_desc = pt_desc1 + "~n".
         end.

         if available ptmstr and ptmstr.pt_desc2 > "" then do:
            ttComponent.pt_desc = ttComponent.pt_desc + pt_desc1.
         end.

         if available ptp_det and ptpRev <> "" then do:
            ttComponent.ptpRev = ptpRev.
         end.
         else if available ptmstr and ptRev <> "" then do:
            ttComponent.ptpRev = pt_rev
         end.
         if bom_code <> ps_comp then do:
            ttComponent.bom_code = bom_code.
         end.

         if level < maxlevel or maxlevel = 0 then do:
            run process_report
               (input ps_comp, input level + 1,input new_parent).
            hSubQuery:get-next().
         end.
         else do:
            hSubQuery:get-next().
         end.
      end.  /* End of Valid date */
      else do:
         hSubQuery:get-next().
      end.
   end.
   hSubQuery:query-close().
   delete object hSubQuery.
END PROCEDURE.

